package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.RentalPayment;

public class DBRentalPayment implements IFDBRentalPayment {
	
	private Connection con;
	private static DBRentalPayment dbRen;
	
	private DBRentalPayment(){
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBRentalPayment getInstance(){
		if(dbRen==null) dbRen = new DBRentalPayment();
		return dbRen;
	}
	
	@Override
	public ArrayList<RentalPayment> getAll() {
		return miscWhere("");
	}
	
	@Override
	public ArrayList<RentalPayment> getAll_where(String wClause){
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<RentalPayment> searchRandom(String searchF) {
		return miscWhere("paymentId LIKE '"+searchF+"' OR date LIKE '"+searchF+"' OR amount LIKE '"+searchF+"' OR rp.apartId LIKE '"+searchF+"' OR rp.tenantId LIKE '"+searchF+"' OR p.fname LIKE '"+searchF+"' OR p.lname LIKE '"+searchF+"' OR ap.apartNo LIKE '"+searchF+"'");  
	}

	@Override
	public RentalPayment getRentalPaymentById(int paymentID) {
		return singleWhere("paymentID="+paymentID,true);
	}
	
	
	@Override
	public RentalPayment getRentalPayment_byDateAndApartment(int apartId, Timestamp date) {
		return singleWhere("rp.apartId="+apartId+" AND datepart(month,date)=datepart(month,'"+date+"')",false);
	}
	
	
	
	
	@Override
	public double getCommissionLastMonth(){
		String query = "SELECT SUM(monthlyRent*commission/100) FROM RentalPayment rp INNER JOIN Contract c ON rp.apartId=c.apartId WHERE MONTH(date)=MONTH(dateadd(mm,-1,getdate()))";
		
		return countQuery(query);
	}
	
	@Override
	public double getCommissionThisMonth(){
		String query = "SELECT SUM(monthlyRent*commission/100) FROM RentalPayment rp INNER JOIN Contract c ON rp.apartId=c.apartId WHERE MONTH(date)=MONTH(getdate())";
		
		return countQuery(query);
	}
	
	@Override
	public double getReceivable(){
		String query = "SELECT SUM(amount) FROM RentalPayment WHERE MONTH(date)=MONTH(getdate())";
		
		return countQuery(query);
	}
	
	
	private double countQuery(String query)
	{
		double count=0;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next()) count = rs.getDouble(1);
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		
		return count;
	}
	
	
	@Override
	public int insert(RentalPayment rp) {
		return insert(rp.getDate(), rp.getAmount(), rp.getApartment().getApartId(), rp.getTenant().getPid());
	}
	
	
	@Override
	public int insert(Timestamp date, double amount, int apartId, int tenantId) {
		int rc = -1;
		
		try{
			String query = "INSERT INTO RentalPayment (date,amount,apartId,tenantId) VALUES ('"+date+"',"+amount+","+apartId+","+tenantId+")";
			
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the Rental Payment  "+e.getMessage());
		}
		
		return rc;
	}
	

	@Override
	public int update(RentalPayment rp) {
		int rc = -1;
		String query = "UPDATE RentalPayment SET date='"+rp.getDate()+"',amount="+rp.getAmount()+" WHERE paymentId="+rp.getPaymentId();
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the Rental Payment "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int delete(int paymentId) {
		int rc = -1;
		String query = "DELETE FROM RentalPayment WHERE paymentId="+paymentId;
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete the Rental Payment "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	
	private RentalPayment singleWhere(String wClause,boolean incept){
		RentalPayment rp = new RentalPayment();
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				rp = buildRentalPayment(result,incept);
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not execute select Rental Payment query "+e.getMessage());
		}
		
		return rp;
	}
	
	
	private ArrayList<RentalPayment> miscWhere(String wClause){
		ArrayList<RentalPayment> rp = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				rp.add(buildRentalPayment(results,true));
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select Rental Payment query "+e.getMessage());
		}
		
		return rp;
	}
	
	
	private String buildQuery(String wClause){
		String query = "SELECT paymentId,date,amount,rp.apartId,tenantId FROM RentalPayment rp INNER JOIN Apartment ap ON rp.apartId=ap.apartId INNER JOIN People p ON rp.tenantId=p.pid";
		
		if(wClause.length()>0) query += " WHERE "+wClause;

		return query;
	}
	
	
	private RentalPayment buildRentalPayment(ResultSet result,boolean incept)
	{
		RentalPayment rp = new RentalPayment();
		
		try{
			rp.setPaymentId(result.getInt("paymentId"));
			rp.setDate(result.getTimestamp("date"));
			rp.setAmount(result.getDouble("amount"));
			
			if(incept)
			{
				rp.setApartment(DBApartment.getInstance().find(result.getInt("apartId"), false));
				rp.setTenant(DBTenant.getInstance().getTenantById(result.getInt("tenantId")));
			}
		}
		catch(Exception e){
			System.err.println("Could not build the Rental Payment Result "+e.getMessage());
		}
		
		return rp;
	}
	
	
	
}
